package com.zx.mes.poi.hyl.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Administrator
 */
public class POIReadExcelToHtml {

    private static final int INITIAL_CAPACITY = 128;
    private static final Pattern R = Pattern.compile("\\)\\w+</td>");

    public static void main(String[] args) {
        String filePath = "C:\\Users\\Administrator\\Desktop\\222";
        String path = "C:\\Users\\Administrator\\Desktop\\BL.XLS";
        String htmlName = "222";
        String exportPath = filePath + "/" + htmlName;
        File exportFile = new File(exportPath);
        if (exportFile.exists()) {
            deleteFile(exportFile);
        }
        if (!exportFile.exists()) {
            /// 只创建一层目录
            exportFile.mkdir();
        }
        try {

            InputStream inputStream = new FileInputStream(new File(path));
            excelToHtml(filePath, inputStream,  htmlName);

            InputStream is = new FileInputStream(new File(filePath+"\\"+htmlName+".html"));
            byte [] b =new byte[512];
            StringBuilder sb = new StringBuilder();
            while (is.read(b) != -1) {
                sb.append(new String(b));
            }
            writeFile(replace(sb.toString()), filePath + "/" + "333.html");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void excelToHtml(String filePath, InputStream inputStream, String htmlName) throws Exception {
        String exportPath = filePath + "/" + htmlName;
        File exportFile = new File(exportPath);
        if (exportFile.exists()) {
            deleteFile(exportFile);
        }
        if (!exportFile.exists()) {
            exportFile.mkdirs();
        }
        StringBuffer sb = null;
        /// 此WorkbookFactory在POI-3.10版本中使用需要添加dom4j
        Workbook wb = WorkbookFactory.create(inputStream);
        String head = htmlHead(wb);
        ///写入头
        writeFile(head, filePath + "/" + htmlName  + ".html");
        try {
            if (wb instanceof XSSFWorkbook) {
                XSSFWorkbook xWb = (XSSFWorkbook) wb;
                //生成内容
                for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
                    /// 获取Sheet的内容
                    Sheet sheet = wb.getSheetAt(numSheet);
                    if (sheet == null) {
                        continue;
                    }
                    if (sb == null) {
                        sb = new StringBuffer();
                    }
                    sb.append("<div id='" + sheet.getSheetName() + "1' style='position:relative;top:30px;'>\n");
                    int lastRowNum = sheet.getLastRowNum();
                    Map<String, String> map[] = getRowSpanColSpanMap(sheet);
                    sb.append("<table style='border-collapse:collapse;'>\n");
                    String replace = replace(sb.toString());
                    /// 追加写入前部分内容
                    writeFile(replace, filePath + "/" + htmlName  + ".html");
                    sb = null;
                    StringBuffer ssb = null;
                    for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
                        if (ssb == null) {
                            ssb = new StringBuffer();
                        }
                        ssb.append("<tr>\n");
                        String htmlExcel = getExcelInfo(xWb, map, sheet, rowNum, true);
                        ssb.append(htmlExcel);
                        ssb.append("</tr>\n");
                        if (rowNum % 5000 == 0) {
                            /// 每5000行写一次
                            ///追加写入部分内容
                            String s = replace(ssb.toString());
                            writeFile(s, filePath + "/" + htmlName + ".html");
                            /// 防止内存溢出，清空
                            ssb = null;
                        }
                    }
                    if (ssb != null) {
                        ssb.append("</table>\n");
                        ssb.append("  </div>\n");
                    } else {
                        ssb = new StringBuffer();
                        ssb.append("</table>\n");
                        ssb.append("  </div>\n");
                    }
                    ///每不足5000行一次写
                    ///追加写入部分内容
                    writeFile(replace(ssb.toString()), filePath + "/" + htmlName  + ".html");
                    ssb = null;
                }
            } else if (wb instanceof HSSFWorkbook) {
                HSSFWorkbook hWb = (HSSFWorkbook) wb;
                //生成内容
                for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
                    /// 获取Sheet的内容
                    Sheet sheet = wb.getSheetAt(numSheet);
                    if (sheet == null) {
                        continue;
                    }
                    if (sb == null) {
                        sb = new StringBuffer();
                    }
                    sb.append("<div id='" + sheet.getSheetName() + "1' style='position:relative;top:30px;'>\n");
                    int lastRowNum = sheet.getLastRowNum();
                    Map<String, String> map[] = getRowSpanColSpanMap(sheet);
                    sb.append("<table style='border-collapse:collapse;'>\n");

                    ///追加写入前部分内容
                    writeFile(sb.toString(), filePath + "/" + htmlName  + ".html");
                    sb = null;
                    StringBuffer ssb = null;
                    for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
                        if (ssb == null) {
                            ssb = new StringBuffer();
                        }
                        ssb.append("<tr>\n");
                        String htmlExcel = getExcelInfo(hWb, map, sheet, rowNum, true);
                        ssb.append(htmlExcel);
                        ssb.append("</tr>\n");
//                        if (rowNum % 5000 == 0) {
//                            //每5000行写一次
//                            ///追加写入部分内容
//                            writeFile(replace(ssb.toString()), filePath + "/" + htmlName  + ".html");
//                            /// 防止内存溢出，清空
//                            ssb = null;
//                        }
                    }
                    if (ssb != null) {
                        ssb.append("</table>\n");
                        ssb.append("  </div>\n");
                    } else {
                        ssb = new StringBuffer();
                        ssb.append("</table>\n");
                        ssb.append("  </div>\n");
                    }
                    ///不足5000行一次写
                    ///追加写入部分内容
                    /// 在这不替换,会导致样式变形
                    writeFile(ssb.toString(), filePath + "/" + htmlName  + ".html");
                    /// 防止内存溢出，清空
                    ssb = null;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            try {
                ///追加写入最后内容
//                writeFile(htmlBottom(), filePath + "/" + htmlName  + ".html");
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }


    private static void writeFile(String content, String path) {
        FileOutputStream fos = null;
        BufferedWriter bw = null;
        try {
            File file = new File(path);
            fos = new FileOutputStream(file, true);
            bw = new BufferedWriter(new OutputStreamWriter(fos, "utf-8"));
            bw.write(content);
            bw.flush();
        } catch (FileNotFoundException fnfe) {
            fnfe.printStackTrace();
        } catch (IOException ioe) {
            ioe.printStackTrace();
        } finally {
            try {
                if (bw != null) {
                    bw.close();
                }
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException ie) {
            }
        }
    }

    private static String htmlHead(Workbook wb) {
        StringBuffer sb = new StringBuffer();
        sb.append("<!DOCTYPE html>\n");
        sb.append("<html>\n");
        sb.append("<head>\n");
        sb.append("     <meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">\n");
        sb.append("</head>\n");
        sb.append("<body>\n");
        sb.append("<div data-options=\"region:'center',border:false\" style=\"position:fixed; z-index:9999; \">\n");
        sb.append("<div id='viewTabs'>\n");
        //生成目录
        for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
            /// 获取Sheet的内容
            Sheet sheet = wb.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }
            sb.append("<div title='" + sheet.getSheetName() + "' style='padding:5px'></div>\n");
        }
        sb.append("  </div>\n");
        sb.append("  </div>\n");
        return sb.toString();
    }



    private static String getExcelInfo(Workbook wb, Map<String, String> map[], Sheet sheet, int rowNum, boolean isWithStyle) {
        StringBuffer sb = new StringBuffer();
        /// 兼容
        Row row = null;
        /// 兼容
        Cell cell = null;
        row = sheet.getRow(rowNum);
        if (row == null) {
            return sb.append("").toString();
        }
        int lastColNum = row.getLastCellNum();
        for (int colNum = 0; colNum < lastColNum; colNum++) {
            cell = row.getCell(colNum);
            /// 特殊情况 空白的单元格会返回null
            if (cell == null) {
                continue;
            }
            String stringValue = getCellValue(cell);
            if (map[0].containsKey(rowNum + "," + colNum)) {
                String pointString = map[0].get(rowNum + "," + colNum);
                map[0].remove(rowNum + "," + colNum);
                int bottomeRow = Integer.valueOf(pointString.split(",")[0]);
                int bottomeCol = Integer.valueOf(pointString.split(",")[1]);
                int rowSpan = bottomeRow - rowNum + 1;
                int colSpan = bottomeCol - colNum + 1;
                sb.append("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' \n");
            } else if (map[1].containsKey(rowNum + "," + colNum)) {
                map[1].remove(rowNum + "," + colNum);
                continue;
            } else {
                sb.append("<td ");
            }
            // 判断是否需要样式
            if (isWithStyle) {
                /// 处理单元格样式
                dealExcelStyle(wb, sheet, cell, sb, stringValue);
            }
            sb.append(">");
            if (stringValue == null || "".equals(stringValue.trim())) {
                sb.append("   ");
            } else {
                sb.append(stringValue.replace(String.valueOf((char) 160), " "));
            }
            sb.append("</td>\n");
        }

        return sb.toString();
    }


    private static Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) {
        Map<String, String> map0 = new HashMap<String, String>();
        Map<String, String> map1 = new HashMap<String, String>();
        int mergedNum = sheet.getNumMergedRegions();
        CellRangeAddress range = null;
        for (int i = 0; i < mergedNum; i++) {
            range = sheet.getMergedRegion(i);
            int topRow = range.getFirstRow();
            int topCol = range.getFirstColumn();
            int bottomRow = range.getLastRow();
            int bottomCol = range.getLastColumn();
            map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);
            int tempRow = topRow;
            while (tempRow <= bottomRow) {
                int tempCol = topCol;
                while (tempCol <= bottomCol) {
                    map1.put(tempRow + "," + tempCol, "");
                    tempCol++;
                }
                tempRow++;
            }
            map1.remove(topRow + "," + topCol);
        }
        Map[] map = {map0, map1};
        return map;
    }

    /**
     * 获取表格单元格Cell内容
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String result = new String();
        switch (cell.getCellType()) {
            /// 数字类型
            case Cell.CELL_TYPE_NUMERIC:
                /// 处理日期格式、时间格式
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {
                        /// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    /// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    /// 单元格设置成常规
                    if (temp.equals("General")) {
                        format.applyPattern("#");
                    }
                    result = format.format(value);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                /// String类型
                result = cell.getRichStringCellValue().toString();
                break;
            case Cell.CELL_TYPE_BLANK:
                result = "";
                break;
            default:
                result = "";
                break;
        }
        return result;
    }

    /**
     * 处理表格样式
     *
     * @param wb
     * @param sheet
     * @param cell
     * @param sb
     */
    private static void dealExcelStyle(Workbook wb, Sheet sheet, Cell cell, StringBuffer sb, String stringValue) {
        CellStyle cellStyle = cell.getCellStyle();
        if (cellStyle != null) {
            short alignment = cellStyle.getAlignment();
            /// 单元格内容的水平对齐方式
            sb.append("align='" + convertAlignToHtml(alignment) + "' ");
            short verticalAlignment = cellStyle.getVerticalAlignment();
            /// 单元格中内容的垂直排列方式
            sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' ");
            if (wb instanceof XSSFWorkbook) {
                XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont();
                short boldWeight = xf.getBoldweight();
                sb.append("style='");
                /// 字体加粗
                sb.append("font-weight:" + boldWeight + ";");
                /// 字体大小
                sb.append("font-size: " + xf.getFontHeight() / 2 + "%;");
                int columnWidth = sheet.getColumnWidth(cell.getColumnIndex());

                sb.append("width:" + columnWidth + "%");

                XSSFColor xc = xf.getXSSFColor();
                if (xc != null && !"".equals(xc)) {
                    /// 字体颜色
                    sb.append("color:#" + xc.getARGBHex().substring(2) + ";");
                }
                XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor();
                if (bgColor != null && !"".equals(bgColor)) {
                    /// 背景颜色
                    sb.append("background-color:#" + bgColor.getARGBHex().substring(2) + ";");
                }
                sb.append(getBorderStyle(0, cellStyle.getBorderTop(),
                        ((XSSFCellStyle) cellStyle).getTopBorderXSSFColor()));
                sb.append(getBorderStyle(1, cellStyle.getBorderRight(),
                        ((XSSFCellStyle) cellStyle).getRightBorderXSSFColor()));
                sb.append(getBorderStyle(2, cellStyle.getBorderBottom(),
                        ((XSSFCellStyle) cellStyle).getBottomBorderXSSFColor()));
                sb.append(getBorderStyle(3, cellStyle.getBorderLeft(),
                        ((XSSFCellStyle) cellStyle).getLeftBorderXSSFColor()));
            } else if (wb instanceof HSSFWorkbook) {
                HSSFFont hf = ((HSSFCellStyle) cellStyle).getFont(wb);
                short boldWeight = hf.getBoldweight();
                short fontColor = hf.getColor();
                sb.append("style='");
                /// 类HSSFPalette用于求的颜色的国际标准形式
                HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette();
                HSSFColor hc = palette.getColor(fontColor);
                /// 字体加粗
                sb.append("font-weight:" + boldWeight + ";");
                /// 字体大小
                sb.append("font-size: " + hf.getFontHeight() / 2 + "%;");
                String fontColorStr = convertToStardColor(hc);
                if (fontColorStr != null && !"".equals(fontColorStr.trim())) {
                    /// 字体颜色
                    sb.append("color:" + fontColorStr + ";");
                }
                int columnWidth = sheet.getColumnWidth(cell.getColumnIndex());
                sb.append("width:" + columnWidth + "px;");
                short bgColor = cellStyle.getFillForegroundColor();
                hc = palette.getColor(bgColor);
                String bgColorStr = convertToStardColor(hc);
                if (bgColorStr != null && !"".equals(bgColorStr.trim())) {
                    /// 背景颜色
                    sb.append("background-color:" + bgColorStr + ";");
                }
                sb.append(getBorderStyle(palette, 0, cellStyle.getBorderTop(), cellStyle.getTopBorderColor()));
                sb.append(getBorderStyle(palette, 1, cellStyle.getBorderRight(), cellStyle.getRightBorderColor()));
                sb.append(getBorderStyle(palette, 3, cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor()));
                sb.append(getBorderStyle(palette, 2, cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor()));
            }
            sb.append("' ");
        }
    }

    /**
     * 单元格内容的水平对齐方式
     *
     * @param alignment
     * @return
     */
    private static String convertAlignToHtml(short alignment) {
        String align = "left";
        switch (alignment) {
            case CellStyle.ALIGN_LEFT:
                align = "left";
                break;
            case CellStyle.ALIGN_CENTER:
                align = "center";
                break;
            case CellStyle.ALIGN_RIGHT:
                align = "right";
                break;
            default:
                break;
        }
        return align;
    }

    /**
     * 单元格中内容的垂直排列方式
     *
     * @param verticalAlignment
     * @return
     */
    private static String convertVerticalAlignToHtml(short verticalAlignment) {
        String valign = "middle";
        switch (verticalAlignment) {
            case CellStyle.VERTICAL_BOTTOM:
                valign = "bottom";
                break;
            case CellStyle.VERTICAL_CENTER:
                valign = "middle";
                break;
            case CellStyle.VERTICAL_TOP:
                valign = "top";
                break;
            default:
                break;
        }
        return valign;
    }

    private static String convertToStardColor(HSSFColor hc) {
        StringBuffer sb = new StringBuffer("");
        if (hc != null) {
            if (HSSFColor.AUTOMATIC.index == hc.getIndex()) {
                return null;
            }
            sb.append("#");
            for (int i = 0; i < hc.getTriplet().length; i++) {
                sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i])));
            }
        }
        return sb.toString();
    }

    private static String fillWithZero(String str) {
        if (str != null && str.length() < 2) {
            return "0" + str;
        }
        return str;
    }

    static String[] bordesr = {"border-top:", "border-right:", "border-bottom:", "border-left:"};
    static String[] borderStyles = {"solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ",
            "solid ", "solid", "solid", "solid", "solid", "solid"};

    private static String getBorderStyle(HSSFPalette palette, int b, short s, short t) {
        if (s == 0) {
            return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;";
        }
        String borderColorStr = convertToStardColor(palette.getColor(t));
        borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" : borderColorStr;
        return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;";
    }

    private static String getBorderStyle(int b, short s, XSSFColor xc) {
        if (s == 0) {
            return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;";
        }
        if (xc != null && !"".equals(xc)) {
            /// t.getARGBHex();
            String borderColorStr = xc.getARGBHex();
            borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000"
                    : borderColorStr.substring(2);
            return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;";
        }
        return "";
    }


    public static void deleteFile(File file) {
        ///判断文件是否存在
        if (file.exists()) {
            /// 判断是否是文件
            if (file.isFile()) {
                /// 删除文件
                file.delete();
            } else if (file.isDirectory()) {
                ///否则如果它是一个目录
                ///声明目录下所有的文件 files[];
                File[] files = file.listFiles();
                ///遍历目录下所有的文件
                for (int i = 0; i < files.length; i++) {
                    ///把每个文件用这个方法进行迭代
                    deleteFile(files[i]);
                }
                ///删除文件夹
                file.delete();
            }
        }
    }

    public static String replace(String html) {
        List<String> list = new ArrayList<>(INITIAL_CAPACITY);
        Matcher m = R.matcher(html);
        while (m.find()) {
            list.add(m.group().substring(1, m.group().length() - 5));
        }

        for (String str : list) {
            html = html.replaceFirst("\\)" + str, "{{d." + str.substring(0, str.length()) + "}}");
        }
        return html;
    }

}
